﻿
/* replace null data in UDS_Flow_Document table */
UPDATE dbo.UDS_Flow_Document SET Doc_Builder_ID = 0         WHERE Doc_Builder_ID IS NULL;
UPDATE dbo.UDS_Flow_Document SET Doc_Added_Date = getdate() WHERE Doc_Added_Date IS NULL;
UPDATE dbo.UDS_Flow_Document SET Doc_Status = 0             WHERE Doc_Status IS NULL;
UPDATE dbo.UDS_Flow_Document SET Flow_ID = 0                WHERE Flow_ID IS NULL;
UPDATE dbo.UDS_Flow_Document SET Step_ID = 0                WHERE Step_ID IS NULL;
UPDATE dbo.UDS_Flow_Document SET IsRunning = 0              WHERE IsRunning IS NULL;
GO


/* change column definitions as NOT NULL */
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Doc_Builder_ID INT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Doc_Added_Date DATETIME NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Doc_Status INT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Flow_ID INT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Step_ID INT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN IsRunning BIT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Obj_ID INT NOT NULL;
ALTER TABLE dbo.UDS_Flow_Document ALTER COLUMN Obj_Type INT NOT NULL;
GO


/* add default constraints */
IF (object_id(N'DF_UDS_Flow_Document_DocAddedDate') IS NOT NULL)
    ALTER TABLE dbo.UDS_Flow_Document DROP CONSTRAINT [DF_UDS_Flow_Document_DocAddedDate];
IF (object_id(N'DF_UDS_Flow_Document_DocStatus') IS NOT NULL)
    ALTER TABLE dbo.UDS_Flow_Document DROP CONSTRAINT [DF_UDS_Flow_Document_DocStatus];
IF (object_id(N'DF_UDS_Flow_Document_IsRunning') IS NOT NULL)
    ALTER TABLE dbo.UDS_Flow_Document DROP CONSTRAINT [DF_UDS_Flow_Document_IsRunning];
GO
ALTER TABLE dbo.UDS_Flow_Document ADD CONSTRAINT [DF_UDS_Flow_Document_DocAddedDate] DEFAULT(getdate()) FOR Doc_Added_Date;
ALTER TABLE dbo.UDS_Flow_Document ADD CONSTRAINT [DF_UDS_Flow_Document_DocStatus] DEFAULT(0) FOR Doc_Status;
ALTER TABLE dbo.UDS_Flow_Document ADD CONSTRAINT [DF_UDS_Flow_Document_IsRunning] DEFAULT(0) FOR IsRunning;
GO


/* Add foreign keys */
IF (object_id(N'[FK_UDS_Flow_Document_Staff]') IS NOT NULL)
    ALTER TABLE dbo.UDS_Flow_Document DROP CONSTRAINT [FK_UDS_Flow_Document_Staff];
IF (object_id(N'[FK_UDS_Flow_Document_FlowStep]') IS NOT NULL)
    ALTER TABLE dbo.UDS_Flow_Document DROP CONSTRAINT [FK_UDS_Flow_Document_FlowStep];
GO
ALTER TABLE dbo.UDS_Flow_Document ADD CONSTRAINT [FK_UDS_Flow_Document_Staff] FOREIGN KEY (Doc_Builder_ID) REFERENCES dbo.UDS_Staff (Staff_ID);
ALTER TABLE dbo.UDS_Flow_Document ADD CONSTRAINT [FK_UDS_Flow_Document_FlowStep] FOREIGN KEY (Flow_ID, Step_ID) REFERENCES dbo.UDS_Flow_Step(Flow_ID, Step_ID);
GO
